<?php

 
Zend_Loader::loadClass("BaseDBRows");
Zend_Loader::loadClass("Staff");
class Staffs extends BaseDBRows
{
	protected function setUp()
	{
		$this->_tableName = Staff::_tablename;
	}	
	
	public function getStaffsByOrgId($orgId,$idNo="",$status=0,$position="",$searchName="") {
		
		Zend_Loader::loadClass("Document");
		Zend_Loader::loadClass("QueryCreator");
		
		$query = new QueryCreator();
		$query->addSelect("concat(s.last_name,', ',s.first_name,' ',s.middle_name) name, s.*, d.link, d.title, d.description");
		$query->addSelect("ifnull(s.address_id,0) address_id_value,ifnull(s.contact_id,0) contact_id_value");
		$query->addFrom($this->_tableName . " s ");
		$query->addJoin(" left join " . Document::_tablename . " d on s.photo_document_id = d.id ");
		$query->addWhere(" s.org_id = " . $orgId);
		
		// idNo
		if(strlen($idNo) > 0) {
			$query->addWhere("s.identification_no = '" . $idNo . "'");
		}
		// status
		if($status == 1) {
			$query->addWhere("s.date_resigned is null or s.date_resigned = ''");
		}
		elseif($status == 2) {
			$query->addWhere("s.date_resigned is not null");
		}
		// position
		if(strlen($position) > 0) {
			$query->addWhere("s.position = '" . $position . "'");
		}
		// searchName
		if(strlen($searchName) > 0) {
			$query->addWhere("concat(s.first_name,' ',s.last_name,' ',s.middle_name) regexp '" . $searchName . "'");
		}
		
		$db = DBCon::instance();
		// echo  $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		return $result;
		
	}
	
	public function getPositionsByOrgId($orgId) {
	
		Zend_Loader::loadClass("QueryCreator");
		
		$query = new QueryCreator();
		$query->addSelect("position");
		$query->addFrom($this->_tableName . " s ");
		$query->addWhere(" s.org_id = " . $orgId);
		$query->addGroupBy("position");
		$query->addOrderBy("position");
		
		$db = DBCon::instance();
		// echo  $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		return $result;
		
	}
	
	public function getStaffViewByOrgId($orgId,$viewType=0) {
	
		Zend_Loader::loadClass("QueryCreator");
		
		$query = new QueryCreator();
		$query->addFrom($this->_tableName . " s ");
		$query->addWhere(" s.org_id = " . $orgId);
		
		// filter
		if($viewType == 0) {
			$query->addSelect("staff_id, concat(last_name, ', ', first_name) staff_display");
		}
		if($viewType == 1) {
			$query->addSelect("staff_id, concat(last_name, ', ', first_name) staff_display");
			$query->addOrderBy("last_name, first_name");	
		}
		if($viewType == 2) {
			$query->addSelect("staff_id, identification_no staff_display");
			$query->addOrderBy("staff_id");	
		}
		
		$db = DBCon::instance();
		// echo  $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		return $result;
		
	}
	
	
}
?>